package date0702;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;

public class StudentDaoDatabase implements StudentDao{

	@Override
	public boolean save(Student s) throws SQLException, ClassNotFoundException {
		int id=s.getId();
		String name=s.getName();
		char gender=s.getGender();
		String gender2=String.valueOf(gender);
		LocalDate birthdate=s.getBirthdate();
		
		
		String driverClassName = "com.mysql.cj.jdbc.Driver";
		// 对于 MySQL 来说需要设置 useServerPrepStmts 和 cachePrepStmts
		String params = "?serverTimezone=Asia/Shanghai&useSSL=false&useServerPrepStmts=true&cachePrepStmts=true";
		String url = "jdbc:mysql://localhost:3306/ECUT" + params;
		String user = "ecuter";
		String password = "ecuter";

		// 1、加载驱动
		Class.forName(driverClassName);

		// 2、获得连接
		Connection connection = DriverManager.getConnection(url, user, password);

		// 参数占位符只能应用在 DML 和 DQL 语句中
		String SQL = "INSERT INTO t_students ( id , name , gender , birthday ) VALUES ( ? , ? , ? , ? )";

		// 3、获得 PreparedStatement对象
		// 3.1、将 带有 参数占位符 的 SQL 语句发送给数据库服务器
		//                  < < < < < 数据库服务器: 接受SQL、解析SQL、编译SQL、缓存编译后的指令、返回相应指令的标识符
		// 3.2、根据数据库服务器返回的 标识符 创建一个包含了该标识符的 PreparedStatement 对象
		PreparedStatement ps = connection.prepareStatement(SQL);
		System.out.println(ps);

		// 4、设置 参数占位符 的值
		ps.setInt(1, id);
		ps.setString(2, name);
		ps.setString(3, gender2);
		java.sql.Date date = java.sql.Date.valueOf(birthdate);
		ps.setDate(4, date); // setDate( int index , java.sql.Date date )
		// 5、"执行"SQL : 将标识符和参数值发送给数据库服务器、获取数据库服务器的返回结果
		int count = ps.executeUpdate(); // 注意这里的方法不要再传入 SQL 语句
		System.out.println(count);
		// 6、释放资源
		connection.close();
		return true;
	}

	@Override
	public Student find(Integer id) throws ClassNotFoundException, SQLException {
		String driverClassName = "com.mysql.cj.jdbc.Driver";
		// 对于 MySQL 来说需要设置 useServerPrepStmts 和 cachePrepStmts
		String params = "?serverTimezone=Asia/Shanghai&useSSL=false&useServerPrepStmts=true&cachePrepStmts=true";
		String url = "jdbc:mysql://localhost:3306/ECUT" + params;
		String user = "ecuter";
		String password = "ecuter";

		// 1、加载驱动
		Class.forName(driverClassName);

		// 2、获得连接
		Connection connection = DriverManager.getConnection(url, user, password);

		// 参数占位符只能应用在 DML 和 DQL 语句中
		String SQL = "SELECT id , name , gender , birthday FROM t_students WHERE id = ?";
		
		PreparedStatement ps = connection.prepareStatement(SQL);
		System.out.println( ps );
		
		ps.setInt( 1 ,  id );
		ResultSet rs = ps.executeQuery();
		System.out.println( "result set : " + rs );
		Student t = null ;
		
		if( rs.next() ) { // 如果 select 语句返回了数据则结果集就会有数据，则说明
			t = new Student(); 
			
			int idn = rs.getInt( "id" ); // rs.getInt( 1 );
			t.setId(idn);
			
			String name = rs.getString( "name" ) ; // rs.getString( 2 );
			t.setName(name);
			
			String genderString = rs.getString( "gender" ); // rs.getString( 3 );
			if( genderString != null && ( genderString = genderString.trim() ).length() > 0  ) {
				char gender = genderString.charAt( 0 ); // 取字符串中首个字符
				t.setGender(gender);
			}
			
			java.sql.Date date = rs.getDate( "birthday" );  // rs.getDate( 4 )
			if( date != null ) {
				java.time.LocalDate birthdate = date.toLocalDate();
				t.setBirthdate( birthdate );
			}
		}
		connection.close();
		return t;
	}


}
